const { Sequelize, DataTypes } = require('sequelize');
const fs = require('fs-extra');
const path = require('path');
const chalk = require('chalk');
const dotenv = require('dotenv');

class ExpressModelSync {
    constructor() {
        this.sequelize = null;
        this.loadEnvConfig();
        this.config = {
            connection: {
                dialect: 'mysql',
                host: process.env.DB_HOST || 'localhost',
                port: parseInt(process.env.DB_PORT) || 3306,
                database: process.env.DB_NAME || 'okx_robot_db',
                username: process.env.DB_USERNAME || 'root',
                password: process.env.DB_PASSWORD || 'admin123*',
            },
            output: {
                outDir: path.resolve(__dirname, '../../src/models'),
                clean: true
            }
        };
    }

    loadEnvConfig() {
        // 尝试从项目根目录加载环境配置文件
        const projectRoot = path.resolve(__dirname, '../../');
        const baseEnvPath = path.join(projectRoot, '.env');
        const envFilePath = path.join(projectRoot, `.env.${process.env.NODE_ENV || 'development'}`);

        // 首先加载基础 .env 文件
        if (fs.existsSync(baseEnvPath)) {
            dotenv.config({ path: baseEnvPath });
            console.log('✅ 从 .env 文件加载基础配置');
        }

        // 然后加载环境特定的配置文件
        if (fs.existsSync(envFilePath)) {
            dotenv.config({ path: envFilePath, override: true });
            console.log(`✅ 从 .env.${process.env.NODE_ENV || 'development'} 文件加载环境配置`);
        } else {
            console.log(`⚠️  未找到 .env.${process.env.NODE_ENV || 'development'} 文件，使用默认配置`);
        }
    }

    async connect() {
        try {
            this.sequelize = new Sequelize(
                this.config.connection.database,
                this.config.connection.username,
                this.config.connection.password,
                {
                    host: this.config.connection.host,
                    port: this.config.connection.port,
                    dialect: this.config.connection.dialect,
                    logging: false,
                    timezone: '+08:00'
                }
            );

            await this.sequelize.authenticate();
            console.log(chalk.green('✅ 数据库连接成功'));
        } catch (error) {
            console.error(chalk.red('❌ 数据库连接失败:'), error.message);
            throw error;
        }
    }

    async getTableInfo() {
        try {
            const [results] = await this.sequelize.query(`
                SELECT
                    TABLE_NAME,
                    TABLE_COMMENT
                FROM
                    information_schema.TABLES
                WHERE
                    TABLE_SCHEMA = '${this.config.connection.database}'
                    AND TABLE_TYPE = 'BASE TABLE'
                ORDER BY
                    TABLE_NAME
            `);

            return results;
        } catch (error) {
            console.error(chalk.red('❌ 获取表信息失败:'), error.message);
            throw error;
        }
    }

    async getColumnInfo(tableName) {
        try {
            const [results] = await this.sequelize.query(`
                SELECT
                    COLUMN_NAME,
                    DATA_TYPE,
                    IS_NULLABLE,
                    COLUMN_DEFAULT,
                    COLUMN_COMMENT,
                    EXTRA,
                    CHARACTER_MAXIMUM_LENGTH,
                    NUMERIC_PRECISION,
                    NUMERIC_SCALE
                FROM
                    information_schema.COLUMNS
                WHERE
                    TABLE_SCHEMA = '${this.config.connection.database}'
                    AND TABLE_NAME = '${tableName}'
                ORDER BY
                    ORDINAL_POSITION
            `);

            return results;
        } catch (error) {
            console.error(chalk.red(`❌ 获取表 ${tableName} 列信息失败:`), error.message);
            throw error;
        }
    }

    mapDataType(dataType, maxLength, precision, scale) {
        const typeMap = {
            'bigint': 'DataTypes.BIGINT',
            'int': 'DataTypes.INTEGER',
            'smallint': 'DataTypes.SMALLINT',
            'mediumint': 'DataTypes.INTEGER',
            'tinyint': 'DataTypes.TINYINT',
            'decimal': `DataTypes.DECIMAL(${precision || 10}, ${scale || 2})`,
            'float': 'DataTypes.FLOAT',
            'double': 'DataTypes.DOUBLE',
            'bit': 'DataTypes.BOOLEAN',
            'varchar': maxLength ? `DataTypes.STRING(${maxLength})` : 'DataTypes.STRING',
            'char': maxLength ? `DataTypes.CHAR(${maxLength})` : 'DataTypes.CHAR',
            'text': 'DataTypes.TEXT',
            'tinytext': 'DataTypes.TEXT',
            'mediumtext': 'DataTypes.TEXT',
            'longtext': 'DataTypes.TEXT',
            'date': 'DataTypes.DATEONLY',
            'datetime': 'DataTypes.DATE',
            'time': 'DataTypes.TIME',
            'timestamp': 'DataTypes.DATE',
            'year': 'DataTypes.INTEGER',
            'json': 'DataTypes.JSON',
            'enum': 'DataTypes.ENUM'
        };

        return typeMap[dataType.toLowerCase()] || 'DataTypes.STRING';
    }

    generateModelContent(tableName, tableComment, columns) {
        const modelName = this.toPascalCase(tableName);
        const fileName = `${modelName}.js`;

        let content = `const { DataTypes } = require('sequelize');\n`;
        content += `const sequelize = require('../config/database');\n\n`;

        content += `const ${modelName} = sequelize.define('${modelName}', {\n`;

        // 添加字段定义
        columns.forEach((column, index) => {
            const fieldName = this.toCamelCase(column.COLUMN_NAME);
            const dataType = this.mapDataType(
                column.DATA_TYPE,
                column.CHARACTER_MAXIMUM_LENGTH,
                column.NUMERIC_PRECISION,
                column.NUMERIC_SCALE
            );

            content += `  ${fieldName}: {\n`;
            content += `    type: ${dataType},\n`;

            if (column.IS_NULLABLE === 'NO' && !column.COLUMN_DEFAULT) {
                content += `    allowNull: false,\n`;
            }

            if (column.COLUMN_DEFAULT !== null) {
                if (column.DATA_TYPE === 'varchar' || column.DATA_TYPE === 'text') {
                    content += `    defaultValue: '${column.COLUMN_DEFAULT}',\n`;
                } else {
                    content += `    defaultValue: ${column.COLUMN_DEFAULT},\n`;
                }
            }

            if (column.EXTRA === 'auto_increment') {
                content += `    autoIncrement: true,\n`;
            }

            if (column.COLUMN_COMMENT) {
                content += `    comment: '${column.COLUMN_COMMENT}',\n`;
            }

            content += `  }${index < columns.length - 1 ? ',' : ''}\n`;
        });

        content += `}, {\n`;
        content += `  tableName: '${tableName}',\n`;
        content += `  comment: '${tableComment || modelName}',\n`;
        content += `  timestamps: true,\n`;
        content += `  paranoid: true,\n`;
        content += `  underscored: true,\n`;
        content += `  createdAt: 'created_at',\n`;
        content += `  updatedAt: 'updated_at',\n`;
        content += `  deletedAt: 'deleted_at'\n`;
        content += `});\n\n`;

        content += `module.exports = ${modelName};\n`;

        return { fileName, content };
    }

    toPascalCase(str) {
        return str
            .split('_')
            .map(word => word.charAt(0).toUpperCase() + word.slice(1).toLowerCase())
            .join('');
    }

    toCamelCase(str) {
        return str
            .split('_')
            .map((word, index) =>
                index === 0 ? word.toLowerCase() : word.charAt(0).toUpperCase() + word.slice(1).toLowerCase()
            )
            .join('');
    }

    async generateModels() {
        try {
            const tables = await this.getTableInfo();
            console.log(chalk.blue(`📋 发现 ${tables.length} 个数据表`));

            // 确保输出目录存在
            await fs.ensureDir(this.config.output.outDir);

            // 如果设置了清理，则清空目录
            if (this.config.output.clean) {
                await fs.emptyDir(this.config.output.outDir);
                console.log(chalk.yellow('🧹 已清理输出目录'));
            }

            for (const table of tables) {
                console.log(chalk.cyan(`📝 处理表: ${table.TABLE_NAME}`));

                const columns = await this.getColumnInfo(table.TABLE_NAME);
                const { fileName, content } = this.generateModelContent(
                    table.TABLE_NAME,
                    table.TABLE_COMMENT,
                    columns
                );

                const filePath = path.join(this.config.output.outDir, fileName);
                await fs.writeFile(filePath, content, 'utf8');

                console.log(chalk.green(`✅ 生成模型文件: ${fileName}`));
            }

            // 生成索引文件
            await this.generateIndexFile(tables);

        } catch (error) {
            console.error(chalk.red('❌ 生成模型失败:'), error.message);
            throw error;
        }
    }

    async generateIndexFile(tables) {
        const indexContent = tables.map(table => {
            const modelName = this.toPascalCase(table.TABLE_NAME);
            return `const ${modelName} = require('./${modelName}');`;
        }).join('\n') + '\n\nmodule.exports = {\n' +
        tables.map(table => {
            const modelName = this.toPascalCase(table.TABLE_NAME);
            return `  ${modelName}`;
        }).join(',\n') + '\n};';

        const indexPath = path.join(this.config.output.outDir, 'index.js');
        await fs.writeFile(indexPath, indexContent, 'utf8');
        console.log(chalk.green('✅ 生成模型索引文件: index.js'));
    }

    async run() {
        try {
            console.log(chalk.blue('🔄 开始同步数据库模型...\n'));

            await this.connect();
            await this.generateModels();

            console.log(chalk.green('\n🎉 模型同步完成!'));
            console.log(chalk.gray(`输出目录: ${this.config.output.outDir}`));

        } catch (error) {
            console.error(chalk.red('\n❌ 模型同步失败:'), error.message);
            throw error;
        } finally {
            if (this.sequelize) {
                await this.sequelize.close();
            }
        }
    }
}

module.exports = new ExpressModelSync();
